package br.ufrj.neg.evento.model;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;

import org.apache.commons.lang.time.DateFormatUtils;
import org.postgresql.largeobject.LargeObject;
import org.postgresql.largeobject.LargeObjectManager;

import br.ufrj.neg.bean.TraducaoBean;
import br.ufrj.neg.evento.bean.ArtefatoBean;
import br.ufrj.neg.evento.bean.TipoArtefatoBean;
import br.ufrj.neg.model.ServiceLocator;
import br.ufrj.neg.model.TraducaoModel;

import br.ufrj.neg.util.Data;

public class ArtefatoModel {
	public int cadastraArtefato(ArtefatoBean artefato) {
		PreparedStatement ps = null;
		ResultSet rs = null;
		Connection conn = ServiceLocator.getConnection();
		try {
			conn.setAutoCommit(false);

			LargeObjectManager lobj = ((org.postgresql.PGConnection) conn)
					.getLargeObjectAPI();

			// Create a new large object
			long oid = lobj.createLO(LargeObjectManager.READ
					| LargeObjectManager.WRITE);

			// Open the large object for writing
			LargeObject obj = lobj.open(oid, LargeObjectManager.WRITE);
			obj.write(artefato.getArquivoAsByte(), 0,
					artefato.getArquivoAsByte().length);
			obj.close();

			Date dt = new Date();
			long nowLong = dt.getTime();
			String hoje = DateFormatUtils.format(nowLong, "dd/MM/yyyy");

			String sql = "INSERT INTO ARTEFATO( even_cd_id, tiar_cd_id, arte_ds_nome, "
					+ "arte_bl_arquivo, arte_dt_data,arte_tx_descricao) values(?, ?, ?, ?, to_date(?,'dd/mm/yyyy'),?)"
					+ "RETURNING arte_cd_id";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, artefato.getEvento());
			ps.setInt(2, artefato.getTipoArtefato());
			ps.setString(3, artefato.getNome());
			ps.setLong(4, oid);
			ps.setString(5, artefato.getData().getDataBr());
			ps.setString(6, artefato.getDescricao());
			
			rs = ps.executeQuery();
			rs.next();
			int id = -1;
			id = rs.getInt(1);
			artefato.setId(id);
			
			conn.commit();
			
			TraducaoModel.insere(artefato);
			
			/*
			 * 1. Blob blob = result.getBlob("logo_empresa"); 2. byte[] bin =
			 * blob.getBytes(1, (int) blob.length()); 3. ByteArrayInputStream
			 * stream = new ByteArrayInputStream(bin); --------------------- #
			 * byte[] bytes = rs.getBytes("foto"); # return (bytes);
			 */

			/*
			 * sql="SELECT max(nego_cd_id) as \"num\" FROM negociacao"; ps =
			 * conn.prepareStatement(sql); ResultSet rs = ps.executeQuery();
			 * conn.commit(); int idNegocio = -1; while (rs.next()){ idNegocio =
			 * rs.getInt("num"); } NegociacaoElementoModel nem = new
			 * NegociacaoElementoModel(); if
			 * (nem.criaNegociacaoElemento(idNegocio)){ return idNegocio; }else{
			 * return -1; }
			 */
			conn.close();
			ps.close();
			// fis.close();
			return id;
		} catch (Exception e) {
			System.out.println(e);
			return -1;
		} finally {
			try {
				ps.close();
			} catch (Exception e) {
				System.out.println(e);
			}
			try {
				conn.close();
			} catch (Exception e) {
				System.out.println(e);
			}
		}
	}
	
	/**
	 * 
	 * @param artefatoBean Um artefatoBean com o id preenchido.
	 * @return
	 */
	public ArtefatoBean getArtefato(ArtefatoBean artefatoBean) {
		PreparedStatement ps = null;
		Connection conn = ServiceLocator.getConnection();
		ArtefatoBean ab = artefatoBean.getNewInstance();
		// All LargeObject API calls must be within a transaction block
		try {
			conn.setAutoCommit(false);

			// Get the Large Object Manager to perform operations with
			LargeObjectManager lobj = ((org.postgresql.PGConnection) conn)
					.getLargeObjectAPI();
			String sql = "select * from artefato where arte_cd_id = ?";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, artefatoBean.getId());
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				// Open the large object for reading
				long oid = rs.getLong("arte_bl_arquivo");
				LargeObject obj = lobj.open(oid, LargeObjectManager.READ);
				//ab.setDescricao(rs.getString("arte_tx_descricao"));
				ab.setId(artefatoBean.getId());
				ab.setNome(rs.getString("arte_ds_nome"));
				ab.setEvento(rs.getInt("even_cd_id"));
				ab.setTipoArtefato(rs.getInt("tiar_cd_id"));
				ab.setData(new Data(rs.getDate("arte_dt_data")));
				
				TraducaoBean traducao = TraducaoModel.getTraducoesElemento(ab);			
				ab.setDicionario( traducao.getDicionario() );			

				// Read the data
				byte buf[] = new byte[obj.size()];
				obj.read(buf, 0, obj.size());
				ab.setArquivoAsByte(buf);
				obj.close();
			}
			rs.close();
			ps.close();
			conn.close();

			return ab;
		} catch (Exception e) {
			// TODO: handle exception
			System.out.println(e.getMessage());
		}
		return null;

	}
	
	/**
	 * @param artefatoBean que deve ter seu atributo reuniao preenchido
	 * @return
	 */
	public ArtefatoBean getRelatorioReuniao(ArtefatoBean artefatoBean) {
		PreparedStatement ps = null;
		Connection conn = ServiceLocator.getConnection();
		ArtefatoBean ab = artefatoBean.getNewInstance();
		// All LargeObject API calls must be within a transaction block
		try {
			conn.setAutoCommit(false);

			// Get the Large Object Manager to perform operations with
			LargeObjectManager lobj = ((org.postgresql.PGConnection) conn)
					.getLargeObjectAPI();
			String sql = "select * from artefato where even_cd_id = ? and tiar_cd_id = 1";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, artefatoBean.getEvento());
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				// Open the large object for reading
				long oid = rs.getLong("arte_bl_arquivo");
				LargeObject obj = lobj.open(oid, LargeObjectManager.READ);
				//ab.setDescricao(rs.getString("arte_tx_descricao"));
				ab.setId(rs.getInt("arte_cd_id"));
				ab.setNome(rs.getString("arte_ds_nome"));
				ab.setEvento(rs.getInt("even_cd_id"));
				ab.setTipoArtefato(rs.getInt("tiar_cd_id"));
				ab.setData(new Data(rs.getDate("arte_dt_data")));

				TraducaoBean traducao = TraducaoModel.getTraducoesElemento(ab);			
				ab.setDicionario( traducao.getDicionario() );	
				
				// Read the data
				byte buf[] = new byte[obj.size()];
				obj.read(buf, 0, obj.size());
				ab.setArquivoAsByte(buf);
				obj.close();
				break;
			}
			rs.close();
			ps.close();
			conn.close();
			return ab;
		} catch (Exception e) {
			// TODO: handle exception
			System.out.println(e.getMessage());
		}
		return null;

	}

	/**
	 * @param artefatoBean que deve ter seu atributo reuniao preenchido
	 * @return
	 */
	public ArrayList<ArtefatoBean> listaArtefatoporReuniao(ArtefatoBean artefatoBean) {
		PreparedStatement ps = null;
		ResultSet rs = null;
		ArrayList<ArtefatoBean> artefatos = new ArrayList<ArtefatoBean>();
		ArtefatoBean artefato;
		TipoArtefatoModel tam = new TipoArtefatoModel();
		TipoArtefatoBean tab = new TipoArtefatoBean( artefatoBean.getIdioma() );
		Collection<TipoArtefatoBean> tiposArtefatos = tam.listaTipoArtefato(tab);
		Connection conn = ServiceLocator.getConnection();
		try {

			String sql = "select * from artefato " +
						"where even_cd_id = ? " +
						"order by arte_cd_id desc";
			ps = conn.prepareStatement(sql);	
			ps.setInt(1, artefatoBean.getEvento());
			rs = ps.executeQuery();
			int indice = 0;

			while (rs.next()) {
				artefato = artefatoBean.getNewInstance();

				// Read the data
				conn.setAutoCommit(false);
				LargeObjectManager lobj = ((org.postgresql.PGConnection) conn)
						.getLargeObjectAPI();
				long oid = new Long(rs.getLong("arte_bl_arquivo"));
				LargeObject obj = lobj.open(oid, LargeObjectManager.READ);

				byte buf[] = new byte[obj.size()];
				obj.read(buf, 0, obj.size());
				artefato.setArquivoAsByte(buf);
				obj.close();

				//artefato.setDescricao(rs.getString("arte_tx_descricao"));
				artefato.setId(rs.getInt("arte_cd_id"));
				artefato.setNome(rs.getString("arte_ds_nome"));
				artefato.setEvento(rs.getInt("even_cd_id"));
				artefato.setTipoArtefato(rs.getInt("tiar_cd_id"));
				artefato.setData(new Data(rs.getDate("arte_dt_data")));
				artefato.setIndice(indice);
				for (TipoArtefatoBean ta : tiposArtefatos) {
					if (ta.getId() == artefato.getTipoArtefato()) {
						artefato.setTipoArtefatoDescricao(ta.getDescricao());
						break;
					}
				}
				
				TraducaoBean traducao = TraducaoModel.getTraducoesElemento(artefato);			
				artefato.setDicionario( traducao.getDicionario() );	
				
				artefatos.add(artefato);
				indice++;
			}
			return artefatos;
		} catch (Exception e) {
			System.out.println(e);
			return null;
		} finally {
			try {
				rs.close();
			} catch (Exception e) {
				System.out.println(e);
			}
			try {
				ps.close();
			} catch (Exception e) {
				System.out.println(e);
			}
			try {
				conn.close();
			} catch (Exception e) {
				System.out.println(e);
			}
		}
	}

	public boolean removeArtefato(ArtefatoBean artefatoBean) {
		PreparedStatement ps = null;
		Connection conn = ServiceLocator.getConnection();
		try {
			TraducaoModel.remove(artefatoBean);			
			
			String sql = "delete from artefato where arte_cd_id = ?";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, artefatoBean.getId());
			ps.executeUpdate();
			conn.commit();

			return true;
		} catch (Exception e) {
			System.out.println(e);
			return false;
		} finally {
			try {
				ps.close();
			} catch (Exception e) {
				System.out.println(e);
			}
			try {
				conn.close();
			} catch (Exception e) {
				System.out.println(e);
			}
		}
	}

	public boolean removeArtefatoPorReuniao(ArtefatoBean artefatoBean) {
		PreparedStatement ps = null;
		ResultSet rs = null;
		Connection conn = ServiceLocator.getConnection();
		try {
			String sql = "delete from artefato where even_cd_id = ? RETURNING arte_cd_id";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, artefatoBean.getEvento());
			rs = ps.executeQuery();
			
			while (rs.next()) {
				int id = rs.getInt(1);				
				artefatoBean.setId(id);
				TraducaoModel.remove(artefatoBean);				
			}			
			
			conn.commit();

			return true;
		} catch (Exception e) {
			System.out.println(e);
			return false;
		} finally {
			try {
				ps.close();
			} catch (Exception e) {
				System.out.println(e);
			}
			try {
				conn.close();
			} catch (Exception e) {
				System.out.println(e);
			}
		}
	}

	/**
	 * 
	 * @param artefatoBean tem que ter preenchido o id da reuniao 
	 * @param artefatosAtivos
	 * @return
	 */
	public boolean removeArtefatoExcluidosPorReuniao(ArtefatoBean artefatoBean,
			ArrayList<Integer> artefatosAtivos) {
		PreparedStatement ps = null;
		ResultSet rs = null;
		Connection conn = ServiceLocator.getConnection();
		try {
			String sql = "delete from artefato where even_cd_id = ?";
			for (int i = 0; i < artefatosAtivos.size(); i++)
				sql += " and arte_cd_id <> ?";
			sql += " RETURNING arte_cd_id";
			
			ps = conn.prepareStatement(sql);
			ps.setInt(1, artefatoBean.getEvento());
			for (int i = 0; i < artefatosAtivos.size(); i++)
				ps.setInt(i + 2, artefatosAtivos.get(i));
			rs = ps.executeQuery();
			
			ArtefatoBean aB = artefatoBean.getNewInstance();
			while(rs.next()) {
				int id = rs.getInt(1);
				aB.setId(id);
				TraducaoModel.remove(aB);	
			}
						
			conn.commit();

			return true;
		} catch (Exception e) {
			System.out.println(e);
			return false;
		} finally {
			try {
				ps.close();
			} catch (Exception e) {
				System.out.println(e);
			}
			try {
				conn.close();
			} catch (Exception e) {
				System.out.println(e);
			}
		}
	}

}
